package com.metacube.metaconnect.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class CommentDAO {

	// creating an instance variable of Connection for connection to mysql
	// server
	private Connection conn;

	public CommentDAO() {
		conn = CreateConnectionToDatabase.createConnection();
	}

	/**
	 * @param email
	 *            email_id Of User who comments
	 * @param post_id
	 *            post_id of the post on which user commented
	 * @param comment
	 *            content of comment
	 * @return true if comment is successfully posted else return false
	 */
	public boolean insertComment(String email, int post_id, String comment) {

		// creating a query
		String query = "insert into comments(post_id, email_id, comment_content)values(?,?,?)";

		PreparedStatement ps = null;
		try {

			ps = conn.prepareStatement(query);

			ps.setInt(1, post_id);
			ps.setString(2, email);
			ps.setString(3, comment.replace("<", "&lt;").replace(">", "&gt;").replace("&","&amp;"));
			int result = ps.executeUpdate();
			if (result == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * gets all comments on a post
	 * 
	 * @param post_id
	 *            post_id of the post on which all comments are extracted
	 * @return list of comments on the post
	 */
	public ArrayList getAllComments(String post_id) {
		ArrayList comments = new ArrayList();

		int pid = Integer.parseInt(post_id);

		// creating a query
		String query = "select  C.comment_id, C.comment_content,C.email_id, C.time_of_comment, U.name, U.profile_picture_url, U.gender "
				+ "FROM Comments C, userinformation U "
				+ "WHERE C.email_id=U.email_id and C.comment_active=? and U.is_active=? and C.post_id =?  ORDER BY C.time_of_comment";

		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);

			ps.setBoolean(1, true);
			ps.setBoolean(2, true);
			ps.setInt(3, pid);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				if (rs.getString("profile_picture_url") == null) {
					if (rs.getString("gender").equals("Male"))
						comments.add("Upload/male.jpg");
					else
						comments.add("Upload/female.jpg");
				} else
					comments.add("Upload/"
							+ rs.getString("profile_picture_url"));
				comments.add(rs.getString("name"));
				comments.add(rs.getString("comment_content"));
				comments.add(rs.getString("time_of_comment"));
				comments.add(rs.getString("comment_id"));
				comments.add(rs.getString("email_id"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return comments;

	}

	/**
	 * delets a comment
	 * 
	 * @param comment_id
	 *            comment_id of the comment which is going to be deleted
	 */
	public boolean deleteComment(int comment_id) {

		String query = "update comments set comment_active =? where comment_id=? ";

		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);

			ps.setBoolean(1, false);
			ps.setInt(2, comment_id);

			int result = ps.executeUpdate();

			if (result == 1)
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * find total comments on a post
	 * 
	 * @param post_id
	 *            post_id of the post on which all comments are to be extracted
	 * @return total number of comments on a post
	 */
	public int getTotalComments(String post_id) {
		int pid = Integer.parseInt(post_id);

		String query = "select count(*) from comments where post_id=?";

		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);
			ps.setInt(1, pid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
}
